package com.javaweb.dao.impl;

import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Tag;
import com.javaweb.service.dto.ArticleConditionDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;

import java.util.*;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/10/9 9:51
 */

@Repository
public class ArticleDaoImpl implements ArticleDao  {

    @Autowired
    JdbcTemplate jdbcTemplate ;

    @Override
    public int updateStatus(Integer id, Integer status) {
        String sql = "UPDATE tb_article SET status = ? WHERE id=?" ;
        return jdbcTemplate.update(sql, new Object[]{status, id});
    }

    @Override
    public int insert(Article article) {
        // Assert.notNull(article)

        String sql = "INSERT INTO tb_article (user_id, title, content, status, " +
                "description, category_id, create_time, update_time)" +
                " VALUES (?,?,?,?,?,?,?,?)" ;
        Object [] args = new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategoryId(),
                new java.sql.Date(article.getCreateTime().getTime()),
                new java.sql.Date(article.getUpdateTime().getTime())};
        return jdbcTemplate.update(sql, args);
    }

    @Override
    public int updateContent(Integer id, String newContent) {
        // 1. 检查 id 和 newContent 是否为空
        if (id == null || newContent == null || newContent.isEmpty()) {
            return 0; // 如果为空，直接返回0
        }

        // 2. 更新 SQL 语句
        String sql = "UPDATE tb_article SET content = ? WHERE id = ?";

        // 3. 执行更新
        int rowsAffected = jdbcTemplate.update(sql, newContent, id);

        // 4. 返回结果，根据受影响的行数判断,失败返回0，成功返回1。
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        // 1. 检查 id 和 newTitle 是否为空
        if (id == null || newTitle == null || newTitle.isEmpty()) {
            return 0; // 如果为空，直接返回0
        }

        // 2. 更新 SQL 语句
        String sql = "UPDATE tb_article SET title = ? WHERE id = ?";

        // 3. 执行更新
        int rowsAffected = jdbcTemplate.update(sql, newTitle, id);

        // 4. 返回结果，根据受影响的行数判断,失败返回0，成功返回1。
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public int deleteById(Integer id) {
        // 1. 检查 id 是否为空
        if (id == null) {
            return 0; // 如果为空，直接返回 0
        }

        // 2. 删除 SQL 语句
        String sql = "DELETE FROM tb_article WHERE id = ?";

        // 3. 执行删除操作
        int rowsAffected = jdbcTemplate.update(sql, id);

        // 4. 返回结果，根据受影响的行数判断
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        if(userId == null){
            return null;
        }
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.user_id = ?" ;

        return jdbcTemplate.query(sql, new Object[]{userId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setUserId(rs.getInt("user_id"));
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setStatus(rs.getInt("status"));
                            article.setDescription(rs.getString("description"));
                            article.setCategoryId(rs.getInt("category_id"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);
                        }
                        article.setSupportCount(rs.getInt("sup")) ;
                        article.setOpposeCount(rs.getInt("opp")) ;
                    }
                    return new ArrayList<>(articleMap.values());
                });
    }


    @Override
    public int selectId(Integer userId, String title, Date createTime) {
        return 0;
    }

    @Override
    public List<Article> selectByKeyword(String keyword) {
        if (keyword == null || keyword.isEmpty()) {
            return null; // 关键字不能为空
        }

        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.title LIKE ? OR a.content LIKE ? " +
                " ORDER BY a.create_time DESC";

        return jdbcTemplate.query(sql, new Object[]{"%" + keyword + "%", "%" + keyword + "%"},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        if (article == null) {
                            article = new Article();
                            article.setId(articleId);
                            article.setUserId(rs.getInt("user_id"));
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setStatus(rs.getInt("status"));
                            article.setDescription(rs.getString("description"));
                            article.setCategoryId(rs.getInt("category_id"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);
                        }
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    return new ArrayList<>(articleMap.values());
                });
    }


    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        StringBuilder sql = new StringBuilder("SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose), 0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (startTime != null) {
            sql.append(" AND a.create_time >= ?");
            params.add(startTime);
        }
        if (endTime != null) {
            sql.append(" AND a.create_time <= ?");
            params.add(endTime);
        }

        sql.append(" ORDER BY a.create_time DESC");

        return jdbcTemplate.query(sql.toString(), params.toArray(),
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        if (article == null) {
                            article = new Article();
                            article.setId(articleId);
                            article.setUserId(rs.getInt("user_id"));
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setStatus(rs.getInt("status"));
                            article.setDescription(rs.getString("description"));
                            article.setCategoryId(rs.getInt("category_id"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);
                        }
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    return articleMap.isEmpty() ? null : new ArrayList<>(articleMap.values());
                });
    }

    @Override
    public List<Article> selectByTag(Integer tagId) {
        // 1. 检查 tagId 是否为空
        if (tagId == null) {
            return null; // 如果为空，直接返回 null
        }

        // 2. 创建 SQL 查询语句
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id = at.article_id " +
                " LEFT JOIN tb_tag t ON t.id = at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose), 0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id = a.id " +
                " WHERE at.tag_id = ? " +
                " ORDER BY a.create_time DESC";

        // 3. 执行查询并返回结果
        return jdbcTemplate.query(sql, new Object[]{tagId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        if (article == null) {
                            article = new Article();
                            article.setId(articleId);
                            article.setUserId(rs.getInt("user_id"));
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setStatus(rs.getInt("status"));
                            article.setDescription(rs.getString("description"));
                            article.setCategoryId(rs.getInt("category_id"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        Integer tagIdResult = rs.getInt("tag_id");
                        if (tagIdResult != null) {
                            Tag tag = new Tag();
                            tag.setId(tagIdResult);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);
                        }
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    return articleMap.isEmpty() ? null : new ArrayList<>(articleMap.values());
                });
    }

    @Override
    public List<Article> selectByCategory(Integer categoryId) {
        // 1. 检查 categoryId 是否为空
        if (categoryId == null) {
            return null; // 如果 categoryId 为空，直接返回 null
        }

        // 2. 创建 SQL 查询语句
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id = at.article_id " +
                " LEFT JOIN tb_tag t ON t.id = at.tag_id " +
                " LEFT JOIN " +
                " (SELECT article_id, IFNULL(SUM(support), 0) AS sup, IFNULL(SUM(oppose), 0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id = a.id " +
                " WHERE a.category_id = ? " +
                " ORDER BY a.create_time DESC";

        // 3. 执行查询并返回结果
        return jdbcTemplate.query(sql, new Object[]{categoryId}, rs -> {
            Map<Integer, Article> articleMap = new HashMap<>();
            while (rs.next()) {
                Integer articleId = rs.getInt("id");
                Article article = articleMap.get(articleId);
                if (article == null) {
                    article = new Article();
                    article.setId(articleId);
                    article.setUserId(rs.getInt("user_id"));
                    article.setTitle(rs.getString("title"));
                    article.setContent(rs.getString("content"));
                    article.setStatus(rs.getInt("status"));
                    article.setDescription(rs.getString("description"));
                    article.setCategoryId(rs.getInt("category_id"));
                    article.setCreateTime(rs.getDate("create_time"));
                    article.setUpdateTime(rs.getDate("update_time"));
                    article.setTags(new ArrayList<>());
                    articleMap.put(articleId, article);
                }

                // 添加标签
                Integer tagId = rs.getInt("tag_id");
                if (tagId != null) {
                    Tag tag = new Tag();
                    tag.setId(tagId);
                    tag.setName(rs.getString("tag_name"));
                    tag.setCreateTime(rs.getDate("tag_create_time"));
                    tag.setUpdateTime(rs.getDate("tag_update_time"));
                    article.getTags().add(tag);
                }

                // 设置支持数和反对数
                article.setSupportCount(rs.getInt("sup"));
                article.setOpposeCount(rs.getInt("opp"));
            }
            return articleMap.isEmpty() ? null : new ArrayList<>(articleMap.values());
        });
    }

    @Override
    public Article selectById(String articleId) {
        String sql = "SELECT * FROM tb_article WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{articleId}, new BeanPropertyRowMapper<>(Article.class));
    }

    @Override
    public int countByConditions(ArticleConditionDto articleCond) {
        String sql = "SELECT COUNT(*) FROM tb_article WHERE 1=1";
        List<Object> params = new ArrayList<>();

        if (articleCond.getKeyword() != null) {
            sql += " AND title LIKE ?";
            params.add("%" + articleCond.getKeyword() + "%");
        }
        if (articleCond.getStatus() != null) {
            sql += " AND status = ?";
            params.add(articleCond.getStatus());
        }
        return jdbcTemplate.queryForObject(sql, params.toArray(), Integer.class);

    }

    @Override
    public List<Article> selectByConditions(ArticleConditionDto articleCond, int fromIndex, int pageSize) {
        String sql = "SELECT * FROM tb_article WHERE 1=1";
        List<Object> params = new ArrayList<>();

        if (articleCond.getKeyword() != null) {
            sql += " AND title LIKE ?";
            params.add("%" + articleCond.getKeyword() + "%");
        }
        if (articleCond.getStatus() != null) {
            sql += " AND status = ?";
            params.add(articleCond.getStatus());
        }
        sql += " LIMIT ?, ?"; // 添加分页
        params.add(fromIndex);
        params.add(pageSize);

        return jdbcTemplate.query(sql, params.toArray(), new BeanPropertyRowMapper<>(Article.class));
    }

    @Override
    public int updatearticle(Article article) {
        String sql = "UPDATE tb_article SET title=?, content=?, description=?, category_id=?, status=? WHERE id=?";

        return jdbcTemplate.update(sql, article.getTitle(), article.getContent(),
                article.getDescription(), article.getCategoryId(), article.getStatus(), article.getId());
    }

}
